from django.db import connection, connections


class DataService:
    def __init__(self):
        self.entity_handler_map = {
            'demo': self.demo_query_handler
        }

    def get_handler(self, entity):
        return self.entity_handler_map.get(entity.key, self.default_query_handler)

    def default_query_handler(self, entity, ext_filter=None, last_updated_at=None, page=1, page_size=10):
        if not (entity.source_db and entity.source_table):
            return 0, []
        source_fields = entity.fields.filter(source_field__isnull=False).exclude(source_field='').values_list('source_field', flat=True)
        if not source_fields:
            return 0, []
        count_sql = f'select count(*) from {entity.source_table} where 1 = 1'
        sql = f'''
            select
               {", ".join([f'`{field}`' for field in source_fields])}
            from
                {entity.source_table}
            where 1 = 1
        '''
        if entity.sql_filter:
            count_sql += f' and {entity.sql_filter}'
            sql += f' and {entity.sql_filter}'

        if ext_filter:
            count_sql += f' and {ext_filter}'
            sql += f' and {ext_filter}'

        if entity.source_updated_at_field and last_updated_at:
            count_sql += f' and {entity.source_updated_at_field} > {last_updated_at}'
            sql += f' and {entity.source_updated_at_field} > {last_updated_at}'

        if page and page_size:
            sql += f' limit {(page-1) * page_size}, {page_size}'

        total, items = 0, []

        print(sql)
        with connections[entity.source_db].cursor() as cursor:
            cursor.execute(count_sql)
            total = cursor.fetchone()[0]
            cursor.execute(sql)
            columns = [col[0] for col in cursor.description]
            items = [dict(zip(columns, row)) for row in cursor.fetchall()]
        return total, items

    def demo_query_handler(self, entity, ext_filter=None, last_updated_at=None, page=1, page_size=10):
        count_sql = 'select count(*) from datacenter_demo where is_deleted = false'
        sql = '''
            select
                id, name, field1, field2, field3, field4, field5, field6
            from
                datacenter_demo
            where is_deleted = false
        '''
        if ext_filter:
            count_sql += f' and {ext_filter}'
            sql += f' and {ext_filter}'

        if last_updated_at:
            count_sql += f' and updated_at > {last_updated_at}'
            sql += f' and updated_at > {last_updated_at}'

        if page and page_size:
            sql += f' limit {(page-1) * page_size}, {page_size}'

        total, items = 0, []
        with connection.cursor() as cursor:
            cursor.execute(count_sql)
            total = cursor.fetchone()[0]
            cursor.execute(sql)
            columns = [col[0] for col in cursor.description]
            items = [dict(zip(columns, row)) for row in cursor.fetchall()]
        return total, items


data_service = DataService()
